package com.qu.OperatorSqlite.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by quguangle on 2017/10/26.
 * 封装数据库的操作
 */

public class DbHelper extends SQLiteOpenHelper{
    private static final String DB_NAME = "person.db";
    private static final int DB_VERSON = 1;

    public DbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSON);
    }

    public DbHelper(Context context,String name){
        super(context, name, null, DB_VERSON);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    /**
     * 版本升级
     * @param db
     * @param i
     * @param i1
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    /**
     * 获取表名
     * @param cla
     * @return
     */
    private String getTableName(Class<?> cla){
        Annotation[] annotations = cla.getDeclaredAnnotations();
        if(annotations == null && annotations.length == 0){
             new IllegalArgumentException("you must create tableName for bean");
        }
        //遍历类对象中所有的注解
        String tableName = null;
        for(Annotation annotation : annotations){
            if(annotation instanceof Table){
               tableName = ((Table)annotation).tableName();
            }
        }

        if(TextUtils.isEmpty(tableName)){
            new IllegalArgumentException("you must create tableName for bean");
        }
        return tableName;
    }

    /**
     * 对外开发建立数据库
     * @param cla
     */
    public void create(Class<?> cla){
        String tableName = getTableName(cla);
        create(tableName,cla);
    }

    public void create(String tableName,Class<?> cla){
        String sql = " CREATE TABLE IF NOT EXISTS " + tableName +" ";
        String table = "";
        String primary = "";
        Field[] fields = cla.getDeclaredFields();
        //遍历类对象中所有变量的对象
        for(Field field : fields){
            field.setAccessible(true);
            if(field.isAnnotationPresent(PrimaryKey.class)){
                PrimaryKey primaryKey = field.getAnnotation(PrimaryKey.class);
                boolean isAutoincrement = primaryKey.autoincrement();
                String name = primaryKey.column();
                primary = String.format(name + " %s primary key " +
                        (isAutoincrement ? "autoincrement," : ","), getTypeString(field));
            }else if(field.isAnnotationPresent(Column.class)){
                Column column = field.getAnnotation(Column.class);
                String name = column.column();
                table = table + String.format(name + "%s",getTypeString(field));
            }
        }
        if (TextUtils.isEmpty(table))
            return;

        //拼接sql语句
        sql = sql + "(" + primary + table.substring(0, table.length() - 1) + ")";
        getWritableDatabase().execSQL(sql);
    }

    /**
     * 更新数据库
     * @param obj
     * @param tableName
     * @param where
     * @param args
     * @return
     */
    public boolean update(Object obj, String tableName, String where, String[] args){
        if(!isExist(tableName)){
            return false;
        }
        SQLiteDatabase db = null;
        ContentValues value = new ContentValues();
        Class<?> cla = obj.getClass();
        Field[] fields = cla.getDeclaredFields();
        try {
            db = getWritableDatabase();
            for(Field field : fields){
                field.setAccessible(true);
                if(field.isAnnotationPresent(Column.class)){

                    Column column = field.getAnnotation(Column.class);
                    String name = column.column();
                    Object object = field.get(obj);
                    value.put(name,(object == null ? "": object.toString()));
                }else if(field.isAnnotationPresent(PrimaryKey.class)){

                    PrimaryKey column = field.getAnnotation(PrimaryKey.class);
                    boolean isAutoincrement = column.autoincrement();
                    String name = column.column();
                    if(!isAutoincrement){
                        Object object = field.get(obj);
                        value.put(name,(object == null ? "": object.toString()));
                    }
                }
            }
            db.update(tableName,value,where,args);
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }
      return false;
    }

    /**
     * 对外提供更新数据库
     * @param obj
     * @param where
     * @param args
     * @return
     */
    public boolean update(Object obj, String where, String... args) {
        Class<?> cls = obj.getClass();
        String tableName = getTableName(cls);
        return update(obj, tableName, where, args);
    }

    /**
     * 插入数据
     * @param obj
     * @param tableName
     * @return
     */
    public boolean insert(Object obj, String tableName){
        if(!isExist(tableName)){
            return false;
        }
        Class<?> cla = obj.getClass();
        Field[] fields = cla.getDeclaredFields();
        SQLiteDatabase db = null;
        ContentValues value = new ContentValues();
        try {
            db = getWritableDatabase();
            for(Field field : fields){
                field.setAccessible(true);
                if(field.isAnnotationPresent(Column.class)){

                    Column column = field.getAnnotation(Column.class);
                    String name = column.column();
                    Object object = field.get(obj);
                    value.put(name,(object == null ? "" : object.toString()));
                }else if(field.isAnnotationPresent(PrimaryKey.class)){

                    PrimaryKey primarykey = field.getAnnotation(PrimaryKey.class);
                    boolean isAutoincrement = primarykey.autoincrement();
                    String name = primarykey.column();
                    if(!isAutoincrement){
                        Object object = field.get(obj);
                        value.put(name,(object == null ? "" : object.toString()));
                    }
                }
            }
            long insert = db.insert(tableName,"",value);
            return insert != 0 ;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 批量的插入数据
     * @param list
     * @param tableName
     * @param <T>
     * @return
     */
    public <T> boolean insertList(List<T> list, String tableName){
        if(!isExist(tableName)){
            return false;
        }
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            for(Object object : list){
                Class<?> cla = object.getClass();
                Field[] fields = cla.getDeclaredFields();
                ContentValues value = new ContentValues();
                for(Field field : fields){
                    field.setAccessible(true);
                    if(field.isAnnotationPresent(Column.class)){

                        Column column = field.getAnnotation(Column.class);
                        String name = column.column();
                        Object obj = field.get(object);
                        value.put(name,(obj == null ? "" : obj.toString()));

                    }else if(field.isAnnotationPresent(PrimaryKey.class)){

                        PrimaryKey primarykey = field.getAnnotation(PrimaryKey.class);
                        boolean isAutoincrement = primarykey.autoincrement();
                        String name = primarykey.column();
                        if(!isAutoincrement){
                            Object obj = field.get(object);
                            value.put(name,(obj == null ? "" : obj.toString()));
                        }
                    }
                }
                db.insert(tableName,"",value);
            }
            db.setTransactionSuccessful();

        }catch (Exception e){
            e.printStackTrace();
            return false;
        }finally {
            if(db != null && db.isOpen()){
                db.endTransaction();
            }
        }
        return true;
    }

    /**
     * 对外提供插入到数据库
     * @param obj
     * @return
     */
    public boolean insert(Object obj) {
        Class<?> cls = obj.getClass();
        String tableName = getTableName(cls);
        return isExist(tableName) && insert(obj, tableName);
    }


    public <T> boolean insertTransaction(List<T> list, String tableName) {
        return isExist(tableName) && insertList(list, tableName);
    }
    /**
     * 查询数据个数
     * @param cls
     * @return
     */
    public long getCount(Class<?> cls) {
        String tableName = getTableName(cls);
        if (!isExist(tableName))
            return -1;
        SQLiteDatabase db;
        Cursor cursor = null;
        try {
            db = getReadableDatabase();
            cursor = db.rawQuery(String.format("select count(*) from %s", tableName), null);
            cursor.moveToFirst();
            long count = cursor.getLong(0);
            cursor.close();
            return count;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return 0;
    }

    /**
     * 判断数据是否存在
     *
     * @return isDataExist
     */
    @SuppressWarnings("LoopStatementThatDoesntLoop")
    public boolean isDataExist(String tableName, String where) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = null;
        try {
            String sql = String.format("SELECT * FROM %s %s", tableName, where);
            cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                return true; //有城市在数据库已存在，返回true
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return false;
    }

    /**
     * 册除table中的数据
     * @param tableName
     * @return
     */
    public boolean clear(String tableName) {
        if (!isExist(tableName)) {
            return false;
        }
        SQLiteDatabase db = null;
        try {
            db = getReadableDatabase();
            db.execSQL(String.format("DELETE from '%s'", tableName));
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(db!= null && db.isOpen()){
                db.close();
            }
        }
        return false;
    }
    /**
     * 更新字段
     *
     * @param table  表
     * @param column 字段
     * @param value  要更新的值
     * @return 成功或者失败
     */
    public boolean update(String table, String column, Object value, String where) {
        if (!isExist(table)) {
            return false;
        }
        SQLiteDatabase db;
        try {
            db = getReadableDatabase();
            String sql = String.format("UPDATE %s SET %s='%s' %s", table, column, value.toString(), where);
            db.execSQL(sql);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 新增字段
     *
     * @param tableName  tableName
     * @param columnName columnName
     * @param type       type
     * @return true or false
     */
    private boolean alter(String tableName, String columnName, String type) {
        if (!isExist(tableName)) return false;
        SQLiteDatabase db ;
        try {
            db = getWritableDatabase();
            db.execSQL(String.format("ALTER TABLE %s ADD %s %s", tableName, columnName, type));
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 新增字段
     *
     * @param cls cls
     * @return true or false
     */
    public boolean alter(Class<?> cls) {
        String tableName = getTableName(cls);
        if (!isExist(tableName))
            return false;
        Field[] fields = cls.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(Column.class)) {
                Column column = field.getAnnotation(Column.class);
                String name = column.column();
                if (!isColumnExist(tableName, name)) {
                    alter(tableName, name, getTypeString(field));
                }
            }
        }
        return false;
    }

    /**
     * 这个列是否存在
     * @param tableName
     * @param columnName
     * @return
     */
    private boolean isColumnExist(String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;
        SQLiteDatabase db ;
        try {
            db = getReadableDatabase();
            cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0"
                    , null);
            result = cursor != null && cursor.getColumnIndex(columnName) != -1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return result;
    }

    /**
     * 判断数据是否存在
     * @param tableName
     * @return
     */
    public boolean isExist(String tableName){
        if(TextUtils.isEmpty(tableName)){
            return false;
        }
        boolean isExist = false;
        Cursor cursor = null;
        SQLiteDatabase db = null;
        String sql = "select * from sqlite_master where name=" + "'" + tableName + "'";
        try {
            db = getReadableDatabase();
            cursor = db.rawQuery(sql,null);
            if(cursor != null && cursor.getCount() != 0){
                isExist = true;
            }
        }catch (Exception e){
            e.printStackTrace();

        }finally {
            if(cursor != null && !cursor.isClosed()){
                cursor.close();
            }
        }
        return isExist;
    }
    /**
     * 得到批量的数据
     * @param cls
     * @param <T>
     * @return
     */
    public <T> List<T> get(Class<T> cls) {
        return get(cls, null, null, null, 0, 0);
    }

    /**
     *
     * 有条件的查询
     * @param cls
     * @param where
     * @param <T>
     * @return
     */
    public <T> List<T> get(Class<T> cls, String where) {
        return get(cls, where, null, null, 0, 0);
    }

    /**
     * 分页的查询
     * @param cls
     * @param limit
     * @param offset
     * @param <T>
     * @return
     */
    public <T> List<T> get(Class<T> cls, int limit, int offset) {
        return get(cls, null, null, null, limit, offset);
    }

    /**
     * 得到批量的数据从数据库中
     * @param cls
     * @param where 条件
     * @param orderColumn 字段
     * @param orderType 排序
     * @param limit 分页
     * @param offset 个数
     * @param <T>
     * @return
     */
    public <T> List<T> get(Class<T> cls, String where, String orderColumn, String orderType, int limit, int offset) {
        String tableName = getTableName(cls);
        if (!isExist(tableName))
            return null;
        List<T> list = new ArrayList<>();
        SQLiteDatabase db ;
        Cursor cursor = null;
        try {
            db = getReadableDatabase();
            String sql = String.format("Select * from %s", tableName);
            String whereAre = TextUtils.isEmpty(where) ? null : " " + where;
            String orderBy = TextUtils.isEmpty(orderColumn) ? null : String.format(" ORDER BY %s %s", orderColumn, orderType);
            String limitStr = limit == 0 ? null : String.format(" limit %s offset %s", String.valueOf(limit), String.valueOf(offset));
            StringBuilder sb = new StringBuilder();
            sb.append(sql);
            sb.append(TextUtils.isEmpty(limitStr) ? "" : limitStr);
            sb.append(TextUtils.isEmpty(whereAre) ? "" : whereAre);
            sb.append(TextUtils.isEmpty(orderBy) ? "" : orderBy);

            sql = sb.toString();
            cursor = db.rawQuery(sql, null);
            Field[] fields = cls.getDeclaredFields();
            if(cursor != null) {
                while (cursor.moveToNext()) {
                    T t = cls.newInstance();
                    for (Field field : fields) {
                        field.setAccessible(true);
                        String name = "";
                        if (field.isAnnotationPresent(Column.class))

                            name = field.getAnnotation(Column.class).column();
                        else if (field.isAnnotationPresent(PrimaryKey.class))

                            name = field.getAnnotation(PrimaryKey.class).column();
                        if (!TextUtils.isEmpty(name)) {

                            Class<?> type = field.getType();
                            if (type.equals(int.class)) {
                                field.set(t, cursor.getInt(cursor.getColumnIndex(name)));
                            } else if (type.equals(String.class)) {
                                field.set(t, cursor.getString(cursor.getColumnIndex(name)));
                            } else if (type.equals(long.class)) {
                                field.set(t, cursor.getLong(cursor.getColumnIndex(name)));
                            } else if (type.equals(float.class)) {
                                field.set(t, cursor.getFloat(cursor.getColumnIndex(name)));
                            } else if (type.equals(double.class)) {
                                field.set(t, cursor.getDouble(cursor.getColumnIndex(name)));
                            }

                        }
                    }

                    list.add(t);
                }
            }else {
                Log.e("TAG","查询数据为空---------------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return list;
    }

    /**
     * 删除数据
     *
     * @param cls cls
     * @return 成功或者失败
     */
    public boolean delete(Class<?> cls, String where, String... args) {
        String tableName = getTableName(cls);
        if (!isExist(tableName))
            return false;
        SQLiteDatabase db;
        try {
            db = getWritableDatabase();
            int i = db.delete(tableName, where, args);
            return i > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    /**
     * 将类对象中所对应的变量数据类型转换成sqlite中数据类型
     * @param field
     * @return
     */
    private String getTypeString(Field field){
        Class<?> type = field.getType();
        if(type.equals(int.class)){

            return "integer";
        }else if(type.equals(String.class)){

            return "text";
        }else if(type.equals(long.class)){

            return "long";
        }else if(type.equals(float.class)){

            return "feal";
        }else if(type.equals(double.class)){

            return "feal";
        }
        return "varchar";
    }
}
